USE DB_TCC
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'USUARIO')
	DROP TABLE USUARIO
GO
CREATE TABLE USUARIO(
	IDUSUARIO			INTEGER NOT NULL IDENTITY,
	TIPOUSUARIO			CHAR(1) NOT NULL,
	NOMEUSUARIO			VARCHAR(100) NOT NULL,
	SEXO				CHAR(1) NOT NULL,
	RG					VARCHAR(10) NULL,
	CPF					VARCHAR(11) NOT NULL,
	LOGIN				VARCHAR(100) NOT NULL,
	SENHA				VARCHAR(100) NOT NULL,
	EMAIL				VARCHAR(100) NOT NULL,
	ATIVO				CHAR(1) NOT NULL,
	USUARIOINCLUSAO		VARCHAR(100) NOT NULL,
	DTHRINCLUSAO		DATETIME NOT NULL,
	USUARIOALTERACAO	VARCHAR(100) NOT NULL,
	DTHRALTERACAO		DATETIME NOT NULL
	CONSTRAINT PK_USUARIO PRIMARY KEY(IDUSUARIO),
	CONSTRAINT IDX_CPF_UNICO UNIQUE(CPF))
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'CURRICULO')
	DROP TABLE CURRICULO
GO
CREATE TABLE CURRICULO(
	IDCURRICULO				INTEGER NOT NULL IDENTITY,
	IDUSUARIO				INTEGER NOT NULL,
	EMAIL					VARCHAR(100) NOT NULL,
	EMAILSECUNDARIO			VARCHAR(100) NOT NULL,
	PAIS					VARCHAR(50) NOT NULL,
	UF						VARCHAR(50) NOT NULL,
	CIDADE					VARCHAR(100) NOT NULL,
	ENDERECO				VARCHAR(100) NOT NULL,
	BAIRRO					VARCHAR(100) NOT NULL,
	CEP						VARCHAR(8) NOT NULL,
	DDDRESIDENCIAL			INTEGER NULL,
	TELRESIDENCIAL			VARCHAR(8) NULL,
	DDDCELULAR				INTEGER NULL,
	TELCELULAR				VARCHAR(8) NULL,
	DDDCOMERCIAL			INTEGER NULL,
	TELCOMERCIAL			VARCHAR(8) NULL,
	NACIONALIDADE			VARCHAR(100) NOT NULL,
	DTNASCIMENTO			DATETIME NOT NULL,
	ESTADOCIVIL				CHAR(1) NOT NULL,
	QTDFILHOS				INTEGER NOT NULL,
	INDDEFICIENTE			CHAR(1) NOT NULL,
	DESCDEFICIENCIA			VARCHAR(255) NOT NULL,
	OBJETIVOPROFISSIONAL	VARCHAR(255) NOT NULL,
	PRETENSAOSALARIO		NUMERIC(17, 2) NULL,
	DTHRINCLUSAO			DATETIME NOT NULL,
	DTHRALTERACAO			DATETIME NOT NULL
	CONSTRAINT PK_CURRICULO PRIMARY KEY(IDCURRICULO))
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'REDE_SOCIAL')
	DROP TABLE REDE_SOCIAL
GO
CREATE TABLE REDE_SOCIAL(
	IDREDESOCIAL		INTEGER NOT NULL IDENTITY,
	DESCREDESOCIAL		VARCHAR(50) NOT NULL,
	ATIVO				CHAR(1) NOT NULL,
	USUARIOINCLUSAO		VARCHAR(100) NOT NULL,
	DTHRINCLUSAO		DATETIME NOT NULL,
	USUARIOALTERACAO	VARCHAR(100) NOT NULL,
	DTHRALTERACAO		DATETIME NOT NULL
	CONSTRAINT PK_REDE_SOCIAL PRIMARY KEY(IDREDESOCIAL))
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'CURRICULO_REDE_SOCIAL')
	DROP TABLE CURRICULO_REDE_SOCIAL
GO
CREATE TABLE CURRICULO_REDE_SOCIAL(
	IDREDESOCIAL			INTEGER NOT NULL,
	IDCURRICULO				INTEGER NOT NULL,
	LINKREDESOCIAL			VARCHAR(100) NOT NULL,
	DTHRINCLUSAO			DATETIME NOT NULL,
	DTHRALTERACAO			DATETIME NOT NULL
	CONSTRAINT PK_CURRICULO_REDE_SOCIAL PRIMARY KEY(IDREDESOCIAL, IDCURRICULO))
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'EXPERIENCIA')
	DROP TABLE EXPERIENCIA
GO
CREATE TABLE EXPERIENCIA(
	IDEXPERIENCIA	INTEGER NOT NULL IDENTITY,
	IDCURRICULO		INTEGER NOT NULL,
	PAIS			VARCHAR(50) NOT NULL,
	UF				VARCHAR(50) NOT NULL,
	PORTE			VARCHAR(50) NOT NULL,
	SEGMENTO		VARCHAR(50) NOT NULL,
	CARGO			VARCHAR(60) NOT NULL,
	DESCATIVIDADES	VARCHAR(255) NOT NULL,
	DTINICIO		DATETIME NOT NULL,
	DTFIM			DATETIME NOT NULL,
	DTHRINCLUSAO	DATETIME NOT NULL,
	DTHRALTERACAO	DATETIME NOT NULL
	CONSTRAINT PK_EXPERIENCIA PRIMARY KEY(IDEXPERIENCIA))
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'FORMACAO')
	DROP TABLE FORMACAO
GO
CREATE TABLE FORMACAO(
	IDFORMACAO			INTEGER NOT NULL IDENTITY,
	IDCURRICULO			INTEGER NOT NULL,
	INSTITUICAO			VARCHAR(50) NOT NULL,
	PAISINSTITUICAO		VARCHAR(50) NOT NULL,
	UFINSTITUICAO		VARCHAR(50) NOT NULL,
	CIDADEINSTITUICAO	VARCHAR(100) NOT NULL,
	TIPOFORMACAO		VARCHAR(50) NOT NULL,
	CURSO				VARCHAR(100) NOT NULL,
	DURACAOHORAS		INTEGER NULL,
	DTINICIO			DATETIME NOT NULL,
	DTTERMINO			DATETIME NOT NULL,
	INDSITUACAO			CHAR(1) NOT NULL,
	DTHRINCLUSAO		DATETIME NOT NULL,
	DTHRALTERACAO		DATETIME NOT NULL
	CONSTRAINT PK_FORMACAO PRIMARY KEY(IDFORMACAO))
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'CLIENTE')
	DROP TABLE CLIENTE
GO
CREATE TABLE CLIENTE(
	CLI_IDCLIENTE			INTEGER IDENTITY,
	CLI_NMCLIENTE			VARCHAR(100) NOT NULL,
	CLI_DESCCLIENTE			VARCHAR(255) NOT NULL,
	CLI_ICATIVO				CHAR(1) NOT NULL,
	CLI_NMUSUARIOINCLUSAO	VARCHAR(100) NOT NULL,
	CLI_DTHRINCLUSAO		DATETIME NOT NULL,
	CLI_NMUSUARIOALTERACAO	VARCHAR(100) NOT NULL,
	CLI_DTHRALTERACAO		DATETIME NOT NULL,
	CONSTRAINT PK_CLIENTE PRIMARY KEY(CLI_IDCLIENTE))
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'PROCESSO_SELETIVO')
	DROP TABLE PROCESSO_SELETIVO
GO
CREATE TABLE PROCESSO_SELETIVO(
	IDPROCESSOSELETIVO		INTEGER NOT NULL IDENTITY,
	IDCLIENTE				INTEGER NOT NULL,
	NOMEPROCESSOSELETIVO	VARCHAR(100) NOT NULL,
	DESCPROCESSOSELETIVO	VARCHAR(255) NOT NULL,
	DATAPROVA				DATETIME NOT NULL,
	ATIVO					CHAR(1) NOT NULL,
	USUARIOINCLUSAO			VARCHAR(100) NOT NULL,
	DTHRINCLUSAO			DATETIME NOT NULL,
	USUARIOALTERACAO		VARCHAR(100) NOT NULL,
	DTHRALTERACAO			DATETIME NOT NULL
	CONSTRAINT PK_PROCESSO_SELETIVO PRIMARY KEY(IDPROCESSOSELETIVO))
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'MEIO_DIVULGACAO')
	DROP TABLE MEIO_DIVULGACAO
GO
CREATE TABLE MEIO_DIVULGACAO(
	IDMEIODIVULGACAO	INTEGER NOT NULL IDENTITY,
	DESCMEIODIVULGACAO	VARCHAR(50) NOT NULL,
	ATIVO				CHAR(1) NOT NULL,
	USUARIOINCLUSAO		VARCHAR(100) NOT NULL,
	DTHRINCLUSAO		DATETIME NOT NULL,
	USUARIOALTERACAO	VARCHAR(100) NOT NULL,
	DTHRALTERACAO		DATETIME NOT NULL
	CONSTRAINT PK_MEIO_DIVULGACAO PRIMARY KEY(IDMEIODIVULGACAO))
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'PROCESSO_SELETIVO_DIVULGACAO')
	DROP TABLE PROCESSO_SELETIVO_DIVULGACAO
GO
CREATE TABLE PROCESSO_SELETIVO_DIVULGACAO(
	IDPROCESSOSELETIVO	INTEGER NOT NULL,
	IDMEIODIVULGACAO	INTEGER NOT NULL,
	USUARIOINCLUSAO		VARCHAR(100) NOT NULL,
	DTHRINCLUSAO		DATETIME NOT NULL,
	USUARIOALTERACAO	VARCHAR(100) NOT NULL,
	DTHRALTERACAO		DATETIME NOT NULL
	CONSTRAINT PK_PROCESSO_SELETIVO_DIVULGACAO PRIMARY KEY(IDPROCESSOSELETIVO, IDMEIODIVULGACAO))
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'AREA')
	DROP TABLE AREA
GO
CREATE TABLE AREA(
	IDAREA				INTEGER NOT NULL IDENTITY,
	DESCAREA			VARCHAR(50) NOT NULL,
	USUARIOINCLUSAO		VARCHAR(100) NOT NULL,
	DTHRINCLUSAO		DATETIME NOT NULL,
	USUARIOALTERACAO	VARCHAR(100) NOT NULL,
	DTHRALTERACAO		DATETIME NOT NULL
	CONSTRAINT PK_AREA PRIMARY KEY(IDAREA))
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'VAGA')
	DROP TABLE VAGA
GO
CREATE TABLE VAGA(
	IDVAGA				INTEGER NOT NULL IDENTITY,
	IDPROCESSOSELETIVO	INTEGER NOT NULL,
	IDAREA				INTEGER NOT NULL,
	CARGOVAGA			VARCHAR(50) NOT NULL,
	DESCVAGA			VARCHAR(255) NOT NULL,
	QTDEVAGAS			INTEGER NOT NULL,
	ATIVO				CHAR(1) NOT NULL,
	USUARIOINCLUSAO		VARCHAR(100) NOT NULL,
	DTHRINCLUSAO		DATETIME NOT NULL,
	USUARIOALTERACAO	VARCHAR(100) NOT NULL,
	DTHRALTERACAO		DATETIME NOT NULL
	CONSTRAINT PK_VAGA PRIMARY KEY(IDVAGA))
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'PERGUNTA')
	DROP TABLE PERGUNTA
GO
CREATE TABLE PERGUNTA(
	IDPERGUNTA			INTEGER NOT NULL IDENTITY,
	IDAREA				INTEGER NULL,
	DESCPERGUNTA		VARCHAR(255) NOT NULL,
	TIPOPERGUNTA		CHAR(1) NOT NULL,
	OBRIGATORIO			CHAR(1) NOT NULL,
	PADRAO				CHAR(1) NOT NULL,
	ATIVO				CHAR(1) NOT NULL,
	USUARIOINCLUSAO		VARCHAR(100) NOT NULL,
	DTHRINCLUSAO		DATETIME NOT NULL,
	USUARIOALTERACAO	VARCHAR(100) NOT NULL,
	DTHRALTERACAO		DATETIME NOT NULL
	CONSTRAINT PK_PERGUNTA PRIMARY KEY(IDPERGUNTA))
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'RESPOSTA')
	DROP TABLE RESPOSTA
GO
CREATE TABLE RESPOSTA(
	IDRESPOSTA			INTEGER NOT NULL IDENTITY,
	IDPERGUNTA			INTEGER NOT NULL,
	NRORESPOSTA			CHAR(2) NOT NULL,
	DESCRESPOSTA		VARCHAR(255) NOT NULL,
	ATIVO				CHAR(1) NOT NULL,
	USUARIOINCLUSAO		VARCHAR(100) NOT NULL,
	DTHRINCLUSAO		DATETIME NOT NULL,
	USUARIOALTERACAO	VARCHAR(100) NOT NULL,
	DTHRALTERACAO		DATETIME NOT NULL
	CONSTRAINT PK_RESPOSTA PRIMARY KEY(IDRESPOSTA))
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'VAGA_PERGUNTA')
	DROP TABLE VAGA_PERGUNTA
GO
CREATE TABLE VAGA_PERGUNTA(
	IDVAGAPERGUNTA		INTEGER NOT NULL IDENTITY,
	IDVAGA				INTEGER NOT NULL,
	IDPERGUNTA			INTEGER NOT NULL,
	USUARIOINCLUSAO		VARCHAR(100) NOT NULL,
	DTHRINCLUSAO		DATETIME NOT NULL,
	USUARIOALTERACAO	VARCHAR(100) NOT NULL,
	DTHRALTERACAO		DATETIME NOT NULL
	CONSTRAINT PK_VAGA_PERGUNTA PRIMARY KEY(IDVAGAPERGUNTA))
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'CANDIDATURA')
	DROP TABLE CANDIDATURA
GO
CREATE TABLE CANDIDATURA(
	IDCANDIDATURA		INTEGER NOT NULL IDENTITY,
	IDUSUARIO			INTEGER NOT NULL,
	IDVAGA				INTEGER NOT NULL,
	INDAPROVADO			CHAR(1) NOT NULL,
	USUARIOINCLUSAO		VARCHAR(100) NOT NULL,
	DTHRINCLUSAO		DATETIME NOT NULL,
	USUARIOALTERACAO	VARCHAR(100) NOT NULL,
	DTHRALTERACAO		DATETIME NOT NULL
	CONSTRAINT PK_CANDIDATURA PRIMARY KEY(IDCANDIDATURA))
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'CANDIDATURA_RESPOSTA')
	DROP TABLE CANDIDATURA_RESPOSTA
GO
CREATE TABLE CANDIDATURA_RESPOSTA(
	IDVAGAPERGUNTA		INTEGER NOT NULL,
	IDCANDIDATURA		INTEGER NOT NULL,
	IDRESPOSTA			INTEGER NOT NULL,
	DESCRESPOSTA		VARCHAR(255) NULL,
	USUARIOINCLUSAO		VARCHAR(100) NOT NULL,
	DTHRINCLUSAO		DATETIME NOT NULL,
	USUARIOALTERACAO	VARCHAR(100) NOT NULL,
	DTHRALTERACAO		DATETIME NOT NULL)
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'TIPO_AGENDAMENTO')
	DROP TABLE TIPO_AGENDAMENTO
GO
CREATE TABLE TIPO_AGENDAMENTO(
	IDTIPOAGENDAMENTO	INTEGER NOT NULL IDENTITY,
	DESCTIPOAGENDAMENTO	VARCHAR(100) NOT NULL,
	ATIVO				CHAR (1) NOT NULL,
	USUARIOINCLUSAO		VARCHAR(100) NOT NULL,
	DTHRINCLUSAO		DATETIME NOT NULL,
	USUARIOALTERACAO	VARCHAR(100) NOT NULL,
	DTHRALTERACAO		DATETIME NOT NULL
	CONSTRAINT PK_TIPO_AGENDAMENTO PRIMARY KEY(IDTIPOAGENDAMENTO))
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'CANDIDATO_AGENDAMENTO')
	DROP TABLE CANDIDATO_AGENDAMENTO
GO
CREATE TABLE CANDIDATO_AGENDAMENTO(
	IDCANDIDATURA		INTEGER NOT NULL,
	IDTIPOAGENDAMENTO	INTEGER NOT NULL,
	DESCAGENDAMENTO		VARCHAR(100) NOT NULL,
	IDSEQUENCIA			INTEGER NOT NULL,
	INDAPROVADO			CHAR(1) NOT NULL,
	USUARIOINCLUSAO		VARCHAR(100) NOT NULL,
	DTHRINCLUSAO		DATETIME NOT NULL,
	USUARIOALTERACAO	VARCHAR(100) NOT NULL,
	DTHRALTERACAO		DATETIME NOT NULL
	CONSTRAINT PK_CANDIDATO_AGENDAMENTO PRIMARY KEY(IDCANDIDATURA, IDTIPOAGENDAMENTO))